From 5111f6996897dd43826bfea16a4f8380cadaacf3 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Sat, 18 Jul 2026 16:08:20 +0000 Subject: [PATCH] Make the housekeeping schema and move AGGRESSION_EVENT_LOG into it --- db/schemas/Makefile | 7 +++-- db/schemas/createschemas.m4 | 2 ++ db/schemas/dropschemas.m4 | 1 + db/schemas/housekeeping/indexes/Makefile | 28 ++++++++++++++++++ .../indexes/create/aggression_event_log.m4 | 0 .../indexes/drop/aggression_event_log.m4 | 0 db/schemas/housekeeping/tables/Makefile | 29 +++++++++++++++++++ .../tables/create/aggression_event_log.m4 | 0 db/schemas/sokwedb/indexes/Makefile | 2 +- db/schemas/sokwedb/tables/Makefile | 1 - doc/src/architecture/schemas.m4 | 9 ++++++ doc/src/housekeeping.m4 | 2 ++ make_files/make_cluster.mk | 6 ++-- 13 files changed, 80 insertions(+), 7 deletions(-) create mode 100644 db/schemas/housekeeping/indexes/Makefile rename db/schemas/{sokwedb => housekeeping}/indexes/create/aggression_event_log.m4 (100%) rename db/schemas/{sokwedb => housekeeping}/indexes/drop/aggression_event_log.m4 (100%) create mode 100644 db/schemas/housekeeping/tables/Makefile rename db/schemas/{sokwedb => housekeeping}/tables/create/aggression_event_log.m4 (100%) diff --git a/db/schemas/Makefile b/db/schemas/Makefile index aad1226..28d7f40 100644 --- a/db/schemas/Makefile +++ b/db/schemas/Makefile @@ -33,16 +33,17 @@ include ../../make_files/help.mk # into the database after the tables they reference, etc. ORDER := codes \ sokwedb \ + housekeeping \ lib # upload # The schemas that contain tables, functions, etc. -TABLE_SCHEMAS := codes sokwedb +TABLE_SCHEMAS := codes sokwedb housekeeping FUNCTION_SCHEMAS := sokwedb TRIGGER_SCHEMAS := lib VIEW_SCHEMAS := sokwedb -INDEX_SCHEMAS := codes sokwedb -COMMENT_SCHEMAS := codes sokwedb +INDEX_SCHEMAS := codes sokwedb housekeeping +COMMENT_SCHEMAS := codes sokwedb housekeeping ## ## CAUTION: This Makefile is not designed to be run directly. It is normally diff --git a/db/schemas/createschemas.m4 b/db/schemas/createschemas.m4 index 8570428..ceb372d 100644 --- a/db/schemas/createschemas.m4 +++ b/db/schemas/createschemas.m4 @@ -23,10 +23,12 @@ dnl CREATE SCHEMA IF NOT EXISTS lib; CREATE SCHEMA IF NOT EXISTS codes; +CREATE SCHEMA IF NOT EXISTS housekeeping; CREATE SCHEMA IF NOT EXISTS sokwedb; CREATE SCHEMA IF NOT EXISTS upload; GRANT USAGE ON SCHEMA lib TO sdb_reader, sdb_writer; GRANT USAGE ON SCHEMA codes TO sdb_reader, sdb_writer; +GRANT USAGE ON SCHEMA housekeeping TO sdb_reader, sdb_writer; GRANT USAGE ON SCHEMA sokwedb TO sdb_reader, sdb_writer; GRANT USAGE ON SCHEMA upload TO sdb_reader, sdb_writer; diff --git a/db/schemas/dropschemas.m4 b/db/schemas/dropschemas.m4 index 6ad7ff6..5782a59 100644 --- a/db/schemas/dropschemas.m4 +++ b/db/schemas/dropschemas.m4 @@ -21,6 +21,7 @@ include(`copyright.m4')dnl dnl DROP SCHEMA IF EXISTS upload CASCADE; +DROP SCHEMA IF EXISTS housekeeping CASCADE; DROP SCHEMA IF EXISTS sokwedb CASCADE; DROP SCHEMA IF EXISTS codes CASCADE; diff --git a/db/schemas/housekeeping/indexes/Makefile b/db/schemas/housekeeping/indexes/Makefile new file mode 100644 index 0000000..6b03fce --- /dev/null +++ b/db/schemas/housekeeping/indexes/Makefile @@ -0,0 +1,28 @@ +# Copyright (C) 2026 The Meme Factory, Inc. http://www.karlpinc.com/ +# +# This program is free software: you can redistribute it and/or modify +# it under the terms of the GNU Affero General Public License as published by +# the Free Software Foundation, either version 3 of the License, or +# (at your option) any later version. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU Affero General Public License for more details. +# +# You should have received a copy of the GNU Affero General Public License +# along with this program. If not, see . +# +# Makefile for tables +# +# Karl O. Pinc + +# This determines the order in which the indexes are put into the database. +# This is not really important but is needed for other types of db objects. +ORDER := aggression_event_log + +## +## CAUTION: This Makefile is not designed to be run directly. It is normally +## invoked by another Makefile. + +include ../../../../make_files/things.mk diff --git a/db/schemas/sokwedb/indexes/create/aggression_event_log.m4 b/db/schemas/housekeeping/indexes/create/aggression_event_log.m4 similarity index 100% rename from db/schemas/sokwedb/indexes/create/aggression_event_log.m4 rename to db/schemas/housekeeping/indexes/create/aggression_event_log.m4 diff --git a/db/schemas/sokwedb/indexes/drop/aggression_event_log.m4 b/db/schemas/housekeeping/indexes/drop/aggression_event_log.m4 similarity index 100% rename from db/schemas/sokwedb/indexes/drop/aggression_event_log.m4 rename to db/schemas/housekeeping/indexes/drop/aggression_event_log.m4 diff --git a/db/schemas/housekeeping/tables/Makefile b/db/schemas/housekeeping/tables/Makefile new file mode 100644 index 0000000..39b4ffb --- /dev/null +++ b/db/schemas/housekeeping/tables/Makefile @@ -0,0 +1,29 @@ +# Copyright (C) 2026 The Meme Factory, Inc. http://www.karlpinc.com/ +# +# This program is free software: you can redistribute it and/or modify +# it under the terms of the GNU Affero General Public License as published by +# the Free Software Foundation, either version 3 of the License, or +# (at your option) any later version. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU Affero General Public License for more details. +# +# You should have received a copy of the GNU Affero General Public License +# along with this program. If not, see . +# +# Makefile for tables +# +# Karl O. Pinc + +# This determines the order in which the tables are put into the database. +# This is important because the tables containing foreign keys must be put +# into the database after the tables they reference. +ORDER := aggression_event_log + +## +## CAUTION: This Makefile is not designed to be run directly. It is normally +## invoked by another Makefile. + +include ../../../../make_files/things.mk diff --git a/db/schemas/sokwedb/tables/create/aggression_event_log.m4 b/db/schemas/housekeeping/tables/create/aggression_event_log.m4 similarity index 100% rename from db/schemas/sokwedb/tables/create/aggression_event_log.m4 rename to db/schemas/housekeeping/tables/create/aggression_event_log.m4 diff --git a/db/schemas/sokwedb/indexes/Makefile b/db/schemas/sokwedb/indexes/Makefile index 4cc41ad..5973019 100644 --- a/db/schemas/sokwedb/indexes/Makefile +++ b/db/schemas/sokwedb/indexes/Makefile @@ -21,7 +21,7 @@ # This is not really important but is needed for other types of db objects. ORDER := biography_data biography_log comm_membs comm_memb_log \ watches follow_observers follow_studies events roles arrivals \ - swelling_sources swelling_states aggression_event_log sightings \ + swelling_sources swelling_states sightings \ aggressions food_events groomings attendance \ arrivals_a species_present repro_states locations_utm \ locations_paper colobus matings fertility elo_ranks_daily_kk_f \ diff --git a/db/schemas/sokwedb/tables/Makefile b/db/schemas/sokwedb/tables/Makefile index 4316652..76ade06 100644 --- a/db/schemas/sokwedb/tables/Makefile +++ b/db/schemas/sokwedb/tables/Makefile @@ -37,7 +37,6 @@ ORDER := biography_data \ species_present \ arrivals \ aggressions \ - aggression_event_log \ non_brec_sighting_sources \ food_events \ groomings \ diff --git a/doc/src/architecture/schemas.m4 b/doc/src/architecture/schemas.m4 index 68ac02d..041eab9 100644 --- a/doc/src/architecture/schemas.m4 +++ b/doc/src/architecture/schemas.m4 @@ -62,6 +62,15 @@ The ``codes`` schema also contains functions and other tools used to administer the system.\ [#f2]_ +The housekeeping schema +``````````````````````` + +The ``housekeeping`` schema contains tables related to the +administration of the database's data. +For the most part, this consists of records of what was updated when. +These are, usually, only of occasional interest. + + The upload schema ````````````````` diff --git a/doc/src/housekeeping.m4 b/doc/src/housekeeping.m4 index 2b14d72..f83bd29 100644 --- a/doc/src/housekeeping.m4 +++ b/doc/src/housekeeping.m4 @@ -28,6 +28,8 @@ Housekeeping Tables The tables listed are used to track data entry history and other administrative operations. +The tables in this section are all in the ``housekeeping`` schema. + .. toctree:: :maxdepth: 3 diff --git a/make_files/make_cluster.mk b/make_files/make_cluster.mk index 7104695..2b0b61f 100644 --- a/make_files/make_cluster.mk +++ b/make_files/make_cluster.mk @@ -1,4 +1,5 @@ -# Copyright (C) 2023, 2025 The Meme Factory, Inc. http://www.karlpinc.com/ +# Copyright (C) 2023, 2025, 2026 The Meme Factory, Inc. +# http://www.karlpinc.com/ # Copyright (C) 2004, 2005, 2008, 2011 Karl O. Pinc # # This program is free software: you can redistribute it and/or modify @@ -226,7 +227,8 @@ CREATE_DB := db/run_createdb.sh # printf statement using them is executed.) # ($$user because make otherwise interprets the $.) SET_SEARCH_PATH := ALTER DATABASE $(TARGET_DB) \ - SET search_path = sokwedb, codes, upload, lib, "$$user"; + SET search_path \ + = sokwedb, codes, upload, lib, housekeeping, "$$user"; SET_DATESTYLE := ALTER DATABASE $(TARGET_DB) \ SET DATESTYLE = 'ISO,MDY'; SET_ISOLATION_LEVEL := ALTER DATABASE $(TARGET_DB) \ -- 2.34.1